from itertools import groupby

from odoo import fields, models
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import UserError


class ProductionDailyReport(models.Model):
    _name = 's.production.daily.report'
    _description = "Production Daily Report"  # 生产日报表
    _auto = False

    station_id = fields.Many2one('work.station', 'Work Station')
    wip_pnl = fields.Integer(string='WIP (PNL)', compute='_compute_wip_pnl')
    qty = fields.Float('PANEL Quantity')
    production_area = fields.Float('Production Area')
    scrap_area = fields.Float('Scrap Area')
    start_date = fields.Datetime('start date')
    end_date = fields.Datetime('end date')

    def _compute_wip_pnl(self):
        wip_records = self.env['mrp.workorder.sum'].search([])
        for rec in self:
            rec.wip_pnl = sum(wip_records.filtered(lambda x: x.work_station_id.id == rec.station_id.id).mapped('qty_wip_pnl'))

    def action_production_daily_report_line_wizard(self):
        product_id = self._context.get('product_id')
        where_product = ''
        if product_id:
            where_product += f' and mw.product_id = {product_id}'
        self.ensure_one()
        start_date = self.start_date
        end_date = self.end_date
        if start_date:
            where_product += f" and mw.date_finished  >= '{start_date}' "
        if end_date:
            where_product += f" and mw.date_finished  <= '{end_date}' "
        query = f"""
            WITH preprocessed_data AS (
                SELECT
                    mp.id production_id,
                    mp.product_id product_id,
                    mw.workcenter_id workcenter_id,
                    ws.id ws_id,
                    mw.qty_op_produced_pnl panel_qty,
                    case when ws.code in ('FQC', 'FQA', 'PKG') then mw.qty_op_produced_set * setl.uom_value * setw.uom_value / 1000000
                    else mw.qty_op_produced_pnl * pnll.uom_value * pnlw.uom_value / 1000000 end production_area,
                    mw.date_finished done_create,
                    CASE 
                        WHEN EXISTS (
                            SELECT 1
                            FROM mrp_workorder
                            left join mrp_workcenter on mrp_workorder.workcenter_id = mrp_workcenter.id
                            WHERE mrp_workorder.production_id = mw.production_id  -- 同一个生产单
                              AND mrp_workcenter.work_station_id = mwc.work_station_id  -- 同一个工站
                              AND mrp_workorder.state != 'done'                  -- 未完成的小工序
                              and mrp_workcenter.is_enter_bcode is true
                              and mrp_workorder.step = mw.step + 1
                        ) THEN FALSE
                        ELSE true end is_done,
                    ROW_NUMBER() OVER (
                            PARTITION BY mw.production_id, ws.id
                            ORDER BY mw.date_finished desc  
                        ) AS rn
                FROM mrp_workorder mw
                    LEFT JOIN mrp_production mp ON mw.production_id = mp.id
                    LEFT JOIN mrp_workcenter mwc ON mw.workcenter_id = mwc.id
                    LEFT JOIN work_station ws ON ws.id = mwc.s_work_station_id
                    LEFT JOIN product_product pp ON mp.product_id = pp.id
                    LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                    LEFT JOIN product_uom_value pnll ON pnll.product_id = pt.id AND pnll.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLL')
                    LEFT JOIN product_uom_value pnlw ON pnlw.product_id = pt.id AND pnlw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLW')
                    LEFT JOIN product_uom_value setl ON setl.product_id = pt.id AND setl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                    LEFT JOIN product_uom_value setw ON setw.product_id = pt.id AND setw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW')
                    LEFT JOIN product_uom_value puv1 ON puv1.product_id = pt.id AND puv1.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SET')
                    LEFT JOIN product_uom_value puv2 ON puv2.product_id = pt.id AND puv2.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PANEL')
                where ws.id = {self.station_id.id}
                  and mwc.is_enter_bcode is true
                  and mw.qty_op_produced > 0 
                  {where_product}
            ), scrap_area as (
                select ws.id ws_id,
                       mp.id mp_id,
                       sum(case
                           when ws.code in ('FQC', 'FQA', 'PKG') then
                                           mw.qty_op_reject / puv1.uom_value * setl.uom_value * setw.uom_value / 1000000
                           else mw.qty_op_reject / puv2.uom_value * pnll.uom_value * pnlw.uom_value / 1000000 end) area
                from mrp_workorder mw
                         LEFT JOIN mrp_production mp ON mw.production_id = mp.id
                         LEFT JOIN mrp_workcenter mwc ON mw.workcenter_id = mwc.id
                         LEFT JOIN work_station ws ON ws.id = mwc.s_work_station_id
                         LEFT JOIN product_product pp ON mp.product_id = pp.id
                         LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                         LEFT JOIN product_uom_value pnll
                                   ON pnll.product_id = pt.id AND pnll.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLL')
                         LEFT JOIN product_uom_value pnlw
                                   ON pnlw.product_id = pt.id AND pnlw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLW')
                         LEFT JOIN product_uom_value setl
                                   ON setl.product_id = pt.id AND setl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                         LEFT JOIN product_uom_value setw
                                   ON setw.product_id = pt.id AND setw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW')
                         LEFT JOIN product_uom_value puv1
                                   ON puv1.product_id = pt.id AND puv1.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SET')
                         LEFT JOIN product_uom_value puv2
                                   ON puv2.product_id = pt.id AND puv2.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PANEL')
                where ws.active is true
                  and ws.disuse is not true 
                  and mw.qty_op_produced > 0
                  and ws.id = {self.station_id.id}
                  {where_product}
                group by ws.id,mp_id
            
            )
            SELECT
                product_id product_id,
                production_id production_id,
                SUM(panel_qty) AS qty,
                sum(production_area) production_area,
                (select area from scrap_area where ws_id = {self.station_id.id} and mp_id = preprocessed_data.production_id)
            FROM preprocessed_data
            where rn = 1 
              and is_done = true
            GROUP BY product_id, ws_id,production_id
        """
        self._cr.execute(query)
        result = self._cr.fetchall()
        if not result:
            raise UserError('无明细数据！')
        vals_list = []
        key_list = ['product_id', 'production_id', 'panel_qty', 'production_area', 'scrap_area']
        for res in result:
            vals_list.append(dict(zip(key_list, list(res))))

        records = self.env['s.production.daily.report.line'].create(vals_list)
        return {
            'name': '',
            'type': 'ir.actions.act_window',
            'view_mode': 'tree',
            'res_model': 's.production.daily.report.line',
            'domain': [('id', 'in', records.ids)],
            'view_id': self.env.ref('sb_plan.s_production_daily_report_line_tree').id,
            'target': 'new',

        }

    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        work_station_id = self._context.get('work_station_id')
        product_id = self._context.get('product_id')

        where = ''
        if start_date:
            where += f" and mw.date_finished >= '{start_date}' "
        if end_date:
            where += f" and mw.date_finished <= '{end_date}' "
        if work_station_id:
            where += f" and ws.id = {work_station_id}"

        if product_id:
            where += f" and pp.id = {product_id}"

        sql = f"""
            WITH preprocessed_data AS (
                SELECT
                    ws.sequence ws_sequence,
                    ws.id AS station_id,
                    mw.qty_op_produced_pnl,
                    mw.qty_op_produced_set,
                    mw.qty_op_reject_pnl,
                    mw.qty_op_reject_set,
                    case when ws.code in ('FQC', 'FQA', 'PKG') then mw.qty_op_produced / puv1.uom_value * setl.uom_value * setw.uom_value / 1000000
                    else mw.qty_op_produced_pnl * pnll.uom_value * pnlw.uom_value / 1000000 end production_area,
                    CASE 
                        WHEN EXISTS (
                            SELECT 1
                            FROM mrp_workorder
                            left join mrp_workcenter on mrp_workorder.workcenter_id = mrp_workcenter.id
                            WHERE mrp_workorder.production_id = mw.production_id  -- 同一个生产单
                              AND mrp_workcenter.work_station_id = mwc.s_work_station_id  -- 同一个工站
                              AND mrp_workorder.state != 'done'                  -- 未完成的小工序
                              and mrp_workcenter.is_enter_bcode is true
                              and mrp_workorder.step = mw.step + 1
                        ) THEN FALSE
                        ELSE true end is_done,
                    ROW_NUMBER() OVER (
                        PARTITION BY mw.production_id, ws.id  -- 按生产单和工站分组
                        ORDER BY mw.date_finished desc  -- 按完成时间升序（取最后一次报工）
                    ) AS rn
                FROM mrp_workorder mw
                    LEFT JOIN mrp_production mp ON mw.production_id = mp.id
                    LEFT JOIN mrp_workcenter mwc ON mw.workcenter_id = mwc.id
                    LEFT JOIN work_station ws ON ws.id = mwc.s_work_station_id
                    LEFT JOIN product_product pp ON mp.product_id = pp.id
                    LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                    LEFT JOIN product_uom_value pnll ON pnll.product_id = pt.id AND pnll.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLL')
                    LEFT JOIN product_uom_value pnlw ON pnlw.product_id = pt.id AND pnlw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLW')
                    LEFT JOIN product_uom_value setl ON setl.product_id = pt.id AND setl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                    LEFT JOIN product_uom_value setw ON setw.product_id = pt.id AND setw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW')
                    LEFT JOIN product_uom_value puv1 ON puv1.product_id = pt.id AND puv1.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SET')
                    LEFT JOIN product_uom_value puv2 ON puv2.product_id = pt.id AND puv2.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PANEL')
                where ws.active is true 
                  and mw.qty_op_produced > 0 
                  and mwc.is_enter_bcode is true
                {where}
            ),scrap_area as (
                select ws.id ws_id,
                       sum(case
                           when ws.code in ('FQC', 'FQA', 'PKG') then
                                           mw.qty_op_reject / puv1.uom_value * setl.uom_value * setw.uom_value / 1000000
                           else mw.qty_op_reject / puv2.uom_value * pnll.uom_value * pnlw.uom_value / 1000000 end) scrap_area
                from mrp_workorder mw
                         LEFT JOIN mrp_production mp ON mw.production_id = mp.id
                         LEFT JOIN mrp_workcenter mwc ON mw.workcenter_id = mwc.id
                         LEFT JOIN work_station ws ON ws.id = mwc.s_work_station_id
                         LEFT JOIN product_product pp ON mp.product_id = pp.id
                         LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                         LEFT JOIN product_uom_value pnll
                                   ON pnll.product_id = pt.id AND pnll.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLL')
                         LEFT JOIN product_uom_value pnlw
                                   ON pnlw.product_id = pt.id AND pnlw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLW')
                         LEFT JOIN product_uom_value setl
                                   ON setl.product_id = pt.id AND setl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                         LEFT JOIN product_uom_value setw
                                   ON setw.product_id = pt.id AND setw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW')
                         LEFT JOIN product_uom_value puv1
                                   ON puv1.product_id = pt.id AND puv1.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SET')
                         LEFT JOIN product_uom_value puv2
                                   ON puv2.product_id = pt.id AND puv2.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PANEL')
                where ws.active is true
                  and ws.disuse is not true 
                  and mw.qty_op_produced > 0
                  {where}
                group by ws.id
            
            )
            SELECT
                station_id id,
                station_id station_id,
                SUM(qty_op_produced_pnl) AS qty,
                sum(production_area) production_area,
                (select scrap_area from scrap_area where ws_id = preprocessed_data.station_id) scrap_area,
                '{start_date}' AS start_date,
                '{end_date}' AS end_date
            FROM preprocessed_data
            where rn = 1
                and is_done = true
            GROUP BY station_id, ws_sequence
            order by ws_sequence
        """
        return sql
